import delimited "$data/Taxsim/ally.csv", clear
keep if year == 2000 | year == 2008
gen tax = fed_avg + st_avg
keep state_id tax state_abv year
replace year = 2010 if year==2008


replace state_abv = subinstr(state_abv, `"""',  "", .)
drop if state_id == 52 | state_id == 9
ren state_abv state
replace state = trim(state)
merge m:1 state using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
keep tax statefips year
order statefips tax
sort statefips
//keep tax

//now nab state sales taxes
preserve
import excel "$data/taxsim/state_sales_tax_2.xlsx", clear
keep B D
drop if _n<9
drop if _n>51
replace D = "0" if strpos(D, "NO")
destring D, replace force
replace D = 6.1 if B == "Utah"
replace D = 5.2 if B == "Virginia"
ren D salestax
ren B state_full
replace state_full = "California" if strpos(state_full, "California")
merge 1:1 state_full using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen
keep statefips salestax
tempfile sales
save `sales'
restore

merge m:1 statefips using `sales', keep(match) nogen
replace sales = sales/100
replace tax = tax + sales
drop salestax
drop if year==.
reshape wide tax, i(statefips) j(year)
ren statefips stfips
ren tax2000 tax_2000
ren tax2010 tax_2010
save "$temp/state_taxes", replace
export delimited "$model/utilities/state_taxes.csv", replace novarn


//FINAL GRAND ASSEMBLY


use "$temp/skill_prices_all", clear //done elsewhere . . .
ren statefips stfips
****assembly
merge 1:1 stfips using "$temp/state_populations_dynamic", keep(match) nogen
merge 1:1 stfips using "$temp/state_school_chars_dynamic", keep(match) nogen
merge 1:1 stfips using "$temp/state_coli_dynamic", keep(match) nogen
merge 1:1 stfips using "$data/Crosswalks/state_div_crosswalk", keep(match) nogen
merge 1:1 stfips using "$temp/state_tuition", keep(match) nogen
merge 1:1 stfips using "$temp/state_taxes", keep(match) nogen

gen amenity_2000 = log(pop_2000)
gen amenity_2010 = log(pop_2010)

ds amenity*
foreach var in `r(varlist)'{
	su `var'
	replace `var' = `var' - `r(min)'
}
su amenity*

gen region = 1
replace region = 2 if div == 3 | div == 4
replace region = 3 if div == 5 | div == 6 | div == 7
replace region = 4 if div == 8 | div == 9

****exporting
preserve
keep stfips *_2000 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2000", novarn replace
restore

preserve
keep stfips *_2010 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2010", novarn replace
restore


save "$temp/state_dynamics_temp", replace



***********Counterfactual equalizations


**school
use "$temp/state_dynamics_temp", clear
su govt_exp*
su s_t_ratio_2000
replace s_t_ratio_2000 = `r(min)'

su s_t_ratio_2010
replace s_t_ratio_2010 = `r(min)'

gen govt_exp_real_2000 = govt_exp_2000/coli_2000
su govt_exp_real_2000
local temp = `r(max)'
replace govt_exp_2000 = `r(max)' * coli_2000
replace govt_exp_2010 = `r(max)' * coli_2010
drop govt_exp_real*
su govt_exp*

preserve
keep stfips *_2000 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2000_cfact_1", novarn replace
restore

preserve
keep stfips *_2010 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2010_cfact_1", novarn replace
restore


*** school (average)
**school
use "$temp/state_dynamics_temp", clear
su govt_exp*
su s_t_ratio_2000
replace s_t_ratio_2000 = `r(mean)'

su s_t_ratio_2010
replace s_t_ratio_2010 = `r(mean)'

gen govt_exp_real_2000 = govt_exp_2000/coli_2000
su govt_exp_real_2000
local temp = `r(mean)'


gen govt_exp_real_2010 = govt_exp_2010/coli_2010
su govt_exp_real_2010
local temp2 = `r(mean)'


replace govt_exp_2000 = `temp' * coli_2000
replace govt_exp_2010 = `temp2' * coli_2010
drop govt_exp_real*
su govt_exp*

preserve
keep stfips *_2000 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2000_cfact_3", novarn replace
restore

preserve
keep stfips *_2010 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2010_cfact_3", novarn replace
restore


*****tuition
use "$temp/state_dynamics_temp", clear

gen charge_real_2000 = charge_2000
su charge_real_2000
local temp = `r(min)'
replace charge_2000 = `temp'

gen charge_real_2010 = charge_2010
su charge_real_2010
local temp = `r(min)'
replace charge_2010 = `temp'
drop charge_real*

preserve
keep stfips *_2000 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2000_cfact_2", novarn replace
restore

preserve
keep stfips *_2010 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2010_cfact_2", novarn replace
restore


*****tuition
use "$temp/state_dynamics_temp", clear

gen charge_real_2000 = charge_2000
su charge_real_2000
local temp = `r(mean)'
replace charge_2000 = `temp'

gen charge_real_2010 = charge_2010
su charge_real_2010
local temp = `r(mean)'
replace charge_2010 = `temp'
drop charge_real*

preserve
keep stfips *_2000 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2000_cfact_4", novarn replace
restore

preserve
keep stfips *_2010 division region
order stfips skill_price_2 skill_price_coll govt s_t coli pop charge tax div amenity region
export delimited "$dir/Model/utilities/state_chars_2010_cfact_4", novarn replace
restore

*****make some maps of skill prices
gen real_wage_2000 = skill_price_2000 / coli_2000
gen real_wage_2010 = skill_price_2010 / coli_2010

gen real_wage_2000_coll = skill_price_coll_2000 / coli_2000
gen real_wage_2010_coll = skill_price_coll_2010 / coli_2010

ren stfips statefips

merge 1:1 statefips using "$data/Crosswalks/state_fips_crosswalk", keep(match) nogen

//2000
maptile skill_price_2000, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices.png") replace

maptile real_wage_2000, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_real.png") replace

//2010
maptile skill_price_2010, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_2010.png") replace

maptile real_wage_2010, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_real_2010.png") replace

//changes
gen diff = skill_price_2010 - skill_price_2000
gen diff_real = real_wage_2010 - real_wage_2000

maptile diff, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_diffs.png") replace

maptile diff_real, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_diffs_real.png") replace


//now do it for college!
maptile skill_price_coll_2000, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_coll.png") replace

maptile real_wage_2000_coll, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_real_coll.png") replace

//2010
maptile skill_price_coll_2010, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_coll_2010.png") replace

maptile real_wage_2010_coll, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_real_coll_2010.png") replace

//changes
gen diff_coll = skill_price_coll_2010 - skill_price_coll_2000
gen diff_coll_real = real_wage_2010_coll- real_wage_2000_coll

maptile diff_coll, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_diffs_coll.png") replace

maptile diff_coll_real, geo(state) n(9) spopt(mos(thick)) legdecimals(2) rev ///
savegraph("$output/fig4_state_skill_prices_diffs_coll_real.png") replace



//save
save "$temp/state_dynamics_all", replace

